﻿using System;
using System.Collections;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Web.UI.WebControls;
using System.Windows;
using ZhuXingFamilys.Help;
using ZhuXingFamilys.ListComparison.Entity;
using ZhuXingFamilys.MainViewModel;
using ZhuXingFamilys.User;

namespace ZhuXingFamilys.Database
{
    public class SqlserverDatabase
    {
        private static string SqlServerConnString = Properties.Resource.DBConnect;
        SqlConnection conn = new SqlConnection(SqlServerConnString);
        public Hashtable sqlserver_read()
        {
            string username;
            string password;
            string sql = "select * from t_User";    //写入sql


            Hashtable h = new Hashtable();
            using (SqlConnection conn = new SqlConnection(SqlServerConnString))
            {
                try
                {
                    SqlCommand command = new SqlCommand(sql, conn);
                    conn.Open();

                    SqlDataReader reader = command.ExecuteReader();

                    while (reader.Read())
                    {
                        username = reader.GetString(0);
                        password = reader.GetString(1);
                        h.Add(username, password);
                    }
                    return h;
                }
                catch (Exception e)
                {

                    MessageBox.Show(e.Message, "error");
                    return null;
                }
                finally
                {
                    conn.Close();
                }
            }


        }

        /// <summary>  
        /// 查询用户是否已存在  
        /// </summary>  
        /// <param name="uName">用户名</param>  
        /// <returns></returns>  
        private static bool UserIsExist(string uName)
        {
            SqlConnection conn = new SqlConnection(SqlServerConnString);
            SqlCommand cmd = new SqlCommand("select 1 from t_User where uName = '" + uName + "'", conn);
            int i = -1;
            try
            {
                conn.Open();
                i = (int)cmd.ExecuteScalar();
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
            finally
            {
                conn.Close();
            }
            if (i == 0)
                return false;
            return true;  //真的表示用户已经存在  
        }




        public void Sqlserver_insert(string u, string p)
        {
            try
            {

                string insertstring = "insert into t_User (UserName,Password,CreateTime) values('" + u + "','" + p + "',GETDATE() )";
                SqlCommand sqlInsert = new SqlCommand(insertstring, conn);
                conn.Open();
                sqlInsert.ExecuteNonQuery();    //插入数据

            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message, "error");
            }
            finally
            {
                conn.Close();
                conn.Dispose();
            }

        }
        // 登录
        internal T_User Login(string userName, string password)
        {
            try
            {
                DataSet dataSet = new DataSet();
                string SqlStr = "select UserName,Password,CreateTime,Authority from t_User where UserName = '"
                    + userName + "' and Password = '" + password + "' ";
                using (SqlConnection conn = new SqlConnection(SqlServerConnString))
                {
                    conn.Open();
                    SqlDataAdapter FamilyData = new SqlDataAdapter(SqlStr, conn);
                    FamilyData.Fill(dataSet);
                    if (dataSet.Tables[0].Rows.Count != 0)
                    {
                        DataRow row = dataSet.Tables[0].Rows[0];
                        T_User user = new T_User();
                        user.UserName = DataProcessor.ToValue(row, "UserName", "");
                        user.Password = DataProcessor.ToValue(row, "Password", "");
                        user.CreateTime = DataProcessor.ToValue(row, "CreateTime", "");
                        user.Authority = DataProcessor.ToValue(row, "Authority", -1);
                        return user;
                    }
                }
            }
            catch (Exception e)
            {
                MessageBox.Show(e.Message);
                MessageBox.Show("请检查网络！");
            }

            return null;
        }
    }
}
